﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using KPIReport.Library;
using KPIReport.Library.DomainObject.Administration.Catalog;
using KPIReport.Library.Enum;
using KPIReport.Library.Interfaces;

namespace KPIReport.Library.DataAccess.Administration.Catalog
{
   public partial class transaksiReturdtlRepo
    {
       public bool SaveUpload(List<transaksiReturdtl> domains)
       {
           bool result = false;
           using (SqlConnection conn = new SqlConnection(connectionString))
           {
               conn.Open();
               SqlTransaction txn = conn.BeginTransaction();
               try
               {
                   domains.ForEach(delegate(transaksiReturdtl domain)
                   {
                       SqlCommand cmd = conn.CreateCommand();
                       cmd.CommandText = @"INSERT INTO dbo.transaksiReturdtl_temp([Customer Code]
                        ,[Salesman Code]
                        ,[Transaction Number]
                        ,[Transaction Date]
                        ,[Product Code]
                        ,[Product Description]
                        ,[Product Index]
                        ,[Selling Type]
                        ,[Product Quantity]
                        ,[UOM]
                        ,[Default UOM]
                        ,[Product List Price]
                        ,[UOM List Price]
                        ,[Gross Amount]
                        ,[Product Discount]
                        ,[Promo Discount]
                        ,[Sales Discount]
                        ,[Amount]
                        ,[Tax Amount]
                        ,[Amount After Tax]
                        ,[Customer Discount Amount]
                        ,[Reason Code]
                        ,[Reason Description]
                        ,[Remark]
                        ,[Case_Pallet_Tr_Rt]
                        ,[DSP_Case_Tr_Rt]
                        ,[EA_Case_Tr_Rt]
                        ,[Qty_EARt]
                        ,[Qty_Karton_Rt]
                        )
                        VALUES(@Customer_Code
                        , @Salesman_Code
                        , @Transaction_Number
                        , @Transaction_Date
                        , @Product_Code
                        , @Product_Description
                        , @Product_Index
                        , @Selling_Type
                        , @Product_Quantity
                        , @UOM
                        , @Default_UOM
                        , @Product_List_Price
                        , @UOM_List_Price
                        , @Gross_Amount
                        , @Product_Discount
                        , @Promo_Discount
                        , @Sales_Discount
                        , @Amount
                        , @Tax_Amount
                        , @Amount_After_Tax
                        , @Customer_Discount_Amount
                        , @Reason_Code
                        , @Reason_Description
                        , @Remark
                        , @CasePalletTrRt
                        , @DSPCaseTrRt
                        , @EACaseTrRt
                        , @QtyEARt
                        , @QtyKartonRt
                        )";
                       cmd.CommandType = CommandType.Text;

                       //cmd.CommandText = "INSERT INTO dbo.transaksiReturdtl(Customer Code, Salesman Code, Transaction Number, Transaction Date, Product Code, Product Description, Product Index, Selling Type, Product Quantity, UOM, Default UOM, Product List Price, UOM List Price, Gross Amount, Product Discount, Promo Discount, Sales Discount, Amount, Tax Amount, Amount After Tax, Customer Discount Amount, Reason Code, Reason Description, Remark, Case_Pallet_Tr_Rt, DSP_Case_Tr_Rt, EA_Case_Tr_Rt, Qty_EARt, Qty_Karton_Rt, kodeproduk) VALUES(@Customer Code, @Salesman Code, @Transaction Number, @Transaction Date, @Product Code, @Product Description, @Product Index, @Selling Type, @Product Quantity, @UOM, @Default UOM, @Product List Price, @UOM List Price, @Gross Amount, @Product Discount, @Promo Discount, @Sales Discount, @Amount, @Tax Amount, @Amount After Tax, @Customer Discount Amount, @Reason Code, @Reason Description, @Remark, @CasePalletTrRt, @DSPCaseTrRt, @EACaseTrRt, @QtyEARt, @QtyKartonRt, @kodeproduk)";
                       //cmd.CommandText = "SP_transaksiReturdtl_Insert";
                       //cmd.CommandType = CommandType.StoredProcedure;
                       
                       cmd.Parameters.Add("@Customer_Code", SqlDbType.VarChar).Value = (object)domain.Customer_Code ?? DBNull.Value;
                       cmd.Parameters.Add("@Salesman_Code", SqlDbType.VarChar).Value = (object)domain.Salesman_Code ?? DBNull.Value;
                       cmd.Parameters.Add("@Transaction_Number", SqlDbType.VarChar).Value = (object)domain.Transaction_Number ?? DBNull.Value;
                       cmd.Parameters.Add("@Transaction_Date", SqlDbType.DateTime).Value = (object)domain.Transaction_Date ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_Code", SqlDbType.VarChar).Value = (object)domain.Product_Code ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_Description", SqlDbType.VarChar).Value = (object)domain.Product_Description ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_Index", SqlDbType.Float).Value = (object)domain.Product_Index ?? DBNull.Value;
                       cmd.Parameters.Add("@Selling_Type", SqlDbType.VarChar).Value = (object)domain.Selling_Type ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_Quantity", SqlDbType.Float).Value = (object)domain.Product_Quantity ?? DBNull.Value;
                       cmd.Parameters.Add("@UOM", SqlDbType.VarChar).Value = (object)domain.UOM ?? DBNull.Value;
                       cmd.Parameters.Add("@Default_UOM", SqlDbType.VarChar).Value = (object)domain.Default_UOM ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_List_Price", SqlDbType.Float).Value = (object)domain.Product_List_Price ?? DBNull.Value;
                       cmd.Parameters.Add("@UOM_List_Price", SqlDbType.Float).Value = (object)domain.UOM_List_Price ?? DBNull.Value;
                       cmd.Parameters.Add("@Gross_Amount", SqlDbType.Float).Value = (object)domain.Gross_Amount ?? DBNull.Value;
                       cmd.Parameters.Add("@Product_Discount", SqlDbType.Float).Value = (object)domain.Product_Discount ?? DBNull.Value;
                       cmd.Parameters.Add("@Promo_Discount", SqlDbType.Float).Value = (object)domain.Promo_Discount ?? DBNull.Value;
                       cmd.Parameters.Add("@Sales_Discount", SqlDbType.Float).Value = (object)domain.Sales_Discount ?? DBNull.Value;
                       cmd.Parameters.Add("@Amount", SqlDbType.Float).Value = (object)domain.Amount ?? DBNull.Value;
                       cmd.Parameters.Add("@Tax_Amount", SqlDbType.Float).Value = (object)domain.Tax_Amount ?? DBNull.Value;
                       cmd.Parameters.Add("@Amount_After_Tax", SqlDbType.Float).Value = (object)domain.Amount_After_Tax ?? DBNull.Value;
                       cmd.Parameters.Add("@Customer_Discount_Amount", SqlDbType.Float).Value = (object)domain.Customer_Discount_Amount ?? DBNull.Value;
                       cmd.Parameters.Add("@Reason_Code", SqlDbType.VarChar).Value = (object)domain.Reason_Code ?? DBNull.Value;
                       cmd.Parameters.Add("@Reason_Description", SqlDbType.VarChar).Value = (object)domain.Reason_Description ?? DBNull.Value;
                       cmd.Parameters.Add("@Remark", SqlDbType.VarChar).Value = (object)domain.Remark ?? DBNull.Value;
                       cmd.Parameters.Add("@CasePalletTrRt", SqlDbType.Float).Value = (object)domain.CasePalletTrRt ?? DBNull.Value;
                       cmd.Parameters.Add("@DSPCaseTrRt", SqlDbType.Float).Value = (object)domain.DSPCaseTrRt ?? DBNull.Value;
                       cmd.Parameters.Add("@EACaseTrRt", SqlDbType.Float).Value = (object)domain.EACaseTrRt ?? DBNull.Value;
                       cmd.Parameters.Add("@QtyEARt", SqlDbType.Float).Value = (object)domain.QtyEARt ?? DBNull.Value;
                       cmd.Parameters.Add("@QtyKartonRt", SqlDbType.Float).Value = (object)domain.QtyKartonRt ?? DBNull.Value;

                       cmd.Transaction = txn;
                       cmd.ExecuteNonQuery();
                       cmd.Dispose();
                   });

                   SqlCommand cmdDelete = conn.CreateCommand();
                   cmdDelete.CommandText = @"DELETE transaksiReturdtl WHERE [Transaction Number] IN (SELECT [Transaction Number] FROM transaksiReturdtl_temp)";
                   cmdDelete.CommandType = CommandType.Text;

                   cmdDelete.Transaction = txn;
                   cmdDelete.ExecuteNonQuery();
                   cmdDelete.Dispose();

                   SqlCommand cmdInsert = conn.CreateCommand();
                   StringBuilder sb = new StringBuilder();
                   sb.AppendLine("insert into transaksiReturdtl ([Customer Code],[Salesman Code],[Transaction Number], [Product Code],[Product Description],[Product Index],[Selling Type],[Product Quantity],UOM,[Default UOM],[Product List Price],[UOM List Price],[Gross Amount],[Product Discount],[Amount],[Tax Amount],[Amount After Tax],[Customer Discount Amount],[Reason Code],[Reason Description])");
                   sb.AppendLine(" select [Customer Code],[Salesman Code],[Transaction Number], [Product Code],[Product Description],[Product Index],[Selling Type],[Product Quantity],UOM,[Default UOM],[Product List Price],[UOM List Price],[Gross Amount],[Product Discount],[Amount],[Tax Amount],[Amount After Tax],[Customer Discount Amount],[Reason Code],[Reason Description] from transaksiReturdtl_temp");
                   sb.AppendLine(" where YEAR([transaction date]) in (select distinct YEAR([transaction date]) from transaksireturdtl_temp) and MONTH([transaction date]) in(select distinct MONTH([transaction date]) from transaksireturdtl_temp)");
                   
                   
                   cmdInsert.CommandText = sb.ToString();
                   cmdInsert.CommandType = CommandType.Text;
                   cmdInsert.Transaction = txn;
                   cmdInsert.ExecuteNonQuery();
                   cmdInsert.Dispose();

                   result = true;
                   txn.Commit();
               }
               catch (Exception ex)
               {
                   txn.Rollback();
                   throw (ex);
               }
               finally
               {
                   if (conn.State == ConnectionState.Open) conn.Close();
               }


           }
           return result;
       }
    }
}
